Imports System.IO
Imports System.Data.SqlClient
Imports System.Drawing

Public Class Imagen_SQL

    Private Params(0) As SqlClient.SqlParameter
    Private fields(0) As String
    Private tam_par As Integer = 0
    Dim errorMessages As String

    Public Sub Add_Parameters_text(ByVal Field_Name As String, ByVal Value As Object, ByVal dbType As System.Data.SqlDbType, Optional ByVal Size As Integer = 0)
        ReDim Preserve Params(tam_par)
        ReDim Preserve fields(tam_par)
        'Dim arch As File
        'arch = File.OpenText(Value.ToString)
        fields(tam_par) = Field_Name
        If Size = 0 Then
            Params(tam_par) = New SqlClient.SqlParameter("@" & Field_Name, dbType)
        Else
            Params(tam_par) = New SqlClient.SqlParameter("@" & Field_Name, dbType, Size)
        End If
        Dim valor As Object = Value
        If dbType = SqlDbType.Image Then
            Dim ms As New MemoryStream
            'File.ReadAllBytes(Value.ToString)
            'CType(Value, Image).Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)

            Dim arrImage() As Byte = File.ReadAllBytes(Value.ToString) 'ms.GetBuffer
            valor = arrImage
        End If
        Params(tam_par).Value = valor
        tam_par += 1
    End Sub

    Public Sub Add_Parameters(ByVal Field_Name As String, ByVal Value As Object, ByVal dbType As System.Data.SqlDbType, Optional ByVal Size As Integer = 0)

        ReDim Preserve Params(tam_par)
        ReDim Preserve fields(tam_par)

        fields(tam_par) = Field_Name
        If Size = 0 Then
            Params(tam_par) = New SqlClient.SqlParameter("@" & Field_Name, dbType)
        Else
            Params(tam_par) = New SqlClient.SqlParameter("@" & Field_Name, dbType, Size)
        End If


        Dim valor As Object = Value

        If dbType = SqlDbType.Image Then


            Dim ms As New MemoryStream
            CType(Value, Image).Save(ms, System.Drawing.Imaging.ImageFormat.Png)

            Dim arrImage() As Byte = ms.GetBuffer

            valor = arrImage
        End If

        Params(tam_par).Value = valor
        tam_par += 1
    End Sub

    Public Function Load_Image(ByVal Query As String, ByVal Connection As String) As Image
        Dim con As New Conexion_SQL
        con.ConnectDataBase(Connection)
        Dim bits As Byte() = CType(con.QueryGetFieldTmpCnn(Query, Connection), Byte())
        Load_Image = Nothing
        If bits.Length > 0 Then
            Dim memorybits As New MemoryStream(bits)
            Dim bitmap As New Bitmap(memorybits)
            Load_Image = bitmap
        End If
        con.DisconnectDataBase()
    End Function

    Public Function Convert_Image(ByVal Objeto As Object) As Image

        Dim bits As Byte() = CType(Objeto, Byte())
        Convert_Image = Nothing
        If bits.Length > 0 Then
            Dim memorybits As New MemoryStream(bits)
            Dim bitmap As New Bitmap(memorybits)
            Convert_Image = bitmap
        End If

    End Function

    Public Sub Update_Image(ByVal Table As String, ByVal where_condition As String, ByVal Connection As String)

        Dim cnn As New SqlClient.SqlConnection(Connection)
        Dim strSQL As String = "Update " & Table.Trim & " Set "
        Dim cad As String = ""
        For i As Integer = 0 To tam_par - 1
            If i > 0 Then cad += ", "
            cad += fields(i) & "=@" & fields(i)
        Next

        strSQL += cad & " where " & where_condition


        Dim cmd As New SqlClient.SqlCommand(strSQL, cnn)

        For i As Integer = 0 To tam_par - 1
            cmd.Parameters.Add(Params(i))
        Next

        Try
            cnn.Open()
            cmd.ExecuteNonQuery()

        Catch ex As SqlException
            Dim i As Integer
            For i = 0 To ex.Errors.Count - 1
                errorMessages = "Index #" & i.ToString() & ControlChars.NewLine _
                    & "Message: " & ex.Errors(i).Message & ControlChars.NewLine _
                    & "LineNumber: " & ex.Errors(i).LineNumber & ControlChars.NewLine _
                    & "Source: " & ex.Errors(i).Source & ControlChars.NewLine _
                    & "Procedure: " & ex.Errors(i).Procedure & ControlChars.NewLine
            Next i
            'msg.Mostrar(errorMessages.ToString(), "Kidadmin", Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub Save_Image(ByVal Table As String, ByVal Connection As String)

        Dim cnn As New SqlClient.SqlConnection(Connection)
        Dim strSQL As String = "Insert Into " & Table.Trim
        Dim cad1 As String = " ("
        Dim cad2 As String = ") VALUES ("

        For i As Integer = 0 To tam_par - 1
            If i > 0 Then cad1 += ", "
            cad1 += fields(i)

            If i > 0 Then cad2 += ", "
            cad2 += Params(i).ParameterName
        Next
        cad2 += ")"
        strSQL += cad1 + cad2

        Dim cmd As New SqlClient.SqlCommand(strSQL, cnn)

        For i As Integer = 0 To tam_par - 1
            cmd.Parameters.Add(Params(i))
        Next
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()
    End Sub

    Public Sub Clear_Parameters()
        tam_par = 0
        ReDim Params(0)
        ReDim fields(0)
    End Sub

    Public Sub New()
        tam_par = 0
        ReDim Params(0)
        ReDim fields(0)
    End Sub



End Class
